package com.design3.graduatethesissys.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.design3.graduatethesissys.domain.Subject;

@Mapper
public interface SubjectMapper {
	
	@Select("<script>" +
			"SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	su.sbj_describe,\r\n" + 
			"	su.sbj_status,\r\n" + 
			"	su.teh_id,\r\n" + 
			"	te.teh_name \r\n" + 
			"FROM\r\n" + 
			"	`subject` su\r\n" + 
			"	LEFT JOIN teacher te ON su.teh_id = te.teh_id \r\n" + 
			"WHERE\r\n" + 
			"	sbj_status = #{sbj_status}\r\n" + 
			"<if test=\"teh_name!=''\">\r\n" + 
			"		and\r\n" + 
			"	te.teh_name  like \"%\" #{teh_name} \"%\" \r\n" + 
			"</if>\r\n" + 
			"<if test=\"sbj_name!=''\">\r\n" + 
			"		and\r\n" + 
			"	te.sbj_name  like \"%\" #{sbj_name} \"%\" \r\n" + 
			"</if>"
			+ "</script>")
	List<Map<String, Object>> subjectSearch(String sbj_status, String sbj_name, String teh_name);
	
	@Insert("insert into subject values(#{sbj_id}, #{sbj_name}, #{sbj_describe}, #{sbj_status}, #{teh_id})")
	void subjectInsert(Subject subject);
	
	@Update("update subject set sbj_name = #{sbj_name}, sbj_describe = #{sbj_describe}, sbj_status = #{sbj_status} where sbj_id = #{sbj_id}")
	void subjectUpdate(Subject subject);
	
	@Delete("delete from subject where sbj_id = #{sbj_id} and sbj_status = 'ct01'")
	void subjectDelete(String sbj_id);
	
	@Select("SELECT COUNT(*) FROM subject where sbj_status = \"ct03\"")
	int searchCount();

	@Update("update subject set sbj_status = \"ct04\" where sbj_id = #{sbj_id}")
	void updateSubject(String sbj_id);

	@Select("Select \r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	su.sbj_describe,\r\n" + 
			"	su.teh_id,\r\n" + 
			"	te.teh_name\r\n" + 
			"	FROM\r\n" + 
			"  `subject` su\r\n" + 
			"	left join student st on st.stu_subject = su.sbj_id\r\n" + 
			"	LEFT JOIN teacher te ON su.teh_id = te.teh_id\r\n" + 
			"	WHERE st.stu_id = #{userName}")
	Subject searchSubjectByStuId(String userName);

	@Select("SELECT COUNT(*) FROM subject where sbj_status = \"ct02\"")
	int getUncheckSubject();

	@Select("SELECT COUNT(*) FROM subject where sbj_status in (\"ct03\",\"ct04\")")
	int getCheckSubject();

	@Select("SELECT COUNT(*) FROM subject where sbj_status = \"ct05\"")
	int getRefuseSubject();

	@Select("SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name,\r\n" + 
			"	su.teh_id \r\n" + 
			"FROM\r\n" + 
			"	`subject` su\r\n" + 
			"	LEFT JOIN teacher te ON te.teh_id=su.teh_id\r\n" + 
			"where\r\n" + 
			"	su.sbj_status = \"ct02\"")
	List<Map<String, Object>> uncheckSubjectSearch();
	
	@Select("SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name,\r\n" + 
			"	su.teh_id \r\n" + 
			"FROM\r\n" + 
			"	`subject` su\r\n" + 
			"	LEFT JOIN teacher te ON te.teh_id=su.teh_id\r\n" + 
			"where\r\n" + 
			"	su.sbj_status in (\"ct03\",\"ct04\")")
	List<Map<String, Object>> checkSubjectSearch();
	
	@Select("SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name,\r\n" + 
			"	su.teh_id \r\n" + 
			"FROM\r\n" + 
			"	`subject` su\r\n" + 
			"	LEFT JOIN teacher te ON te.teh_id=su.teh_id\r\n" + 
			"where\r\n" + 
			"	su.sbj_status = \"ct05\"")
	List<Map<String, Object>> refuseSubjectSearch();

	@Select("SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" +
			"	te.teh_name,\r\n" +
			"	su.sbj_describe, \r\n" +
			"	su.teh_id \r\n" +
			"FROM\r\n" + 
			"	`subject` su\r\n" + 
			"	LEFT JOIN teacher te ON te.teh_id=su.teh_id\r\n" + 
			"where\r\n" + 
			"	su.sbj_id = #{sub_id}")
	Map<String, Object> getSubDetail(String sub_id);

	@Select("<script>" +
			"SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name\r\n" + 
			"FROM\r\n" + 
			"	`subject` su LEFT join teacher te ON te.teh_id = su.teh_id\r\n" + 
			"where \r\n" + 
			"	su.sbj_status = \"ct02\"\r\n" + 
			"	<if test=\"teachername!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_name like \"%\" #{teachername} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"subname!=''\">\r\n" + 
			"	and\r\n" + 
			"	su.sbj_name  like \"%\" #{subname} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"teacherid!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_id = #{teacherid}\r\n" + 
			"	</if>"
			+ "</script>")
	List<Map<String, Object>> querySubject1(String teachername, String teacherid, String subname);

	@Select("<script>" +
			"SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name\r\n" + 
			"FROM\r\n" + 
			"	`subject` su LEFT join teacher te ON te.teh_id = su.teh_id\r\n" + 
			"where \r\n" + 
			"	su.sbj_status in( \"ct03\", \"ct04\")\r\n" + 
			"	<if test=\"teachername!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_name like \"%\" #{teachername} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"subname!=''\">\r\n" + 
			"	and\r\n" + 
			"	su.sbj_name  like \"%\" #{subname} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"teacherid!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_id = #{teacherid}\r\n" + 
			"	</if>"
			+ "</script>")
	List<Map<String, Object>> querySubject2(String teachername, String teacherid, String subname);
	
	@Select("<script>" +
			"SELECT\r\n" + 
			"	su.sbj_id,\r\n" + 
			"	su.sbj_name,\r\n" + 
			"	te.teh_name\r\n" + 
			"FROM\r\n" + 
			"	`subject` su LEFT join teacher te ON te.teh_id = su.teh_id\r\n" + 
			"where \r\n" + 
			"	su.sbj_status = \"ct05\"\r\n" + 
			"	<if test=\"teachername!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_name like \"%\" #{teachername} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"subname!=''\">\r\n" + 
			"	and\r\n" + 
			"	su.sbj_name  like \"%\" #{subname} \"%\" \r\n" + 
			"	</if>\r\n" + 
			"	<if test=\"teacherid!=''\">\r\n" + 
			"	and\r\n" + 
			"	te.teh_id = #{teacherid}\r\n" + 
			"	</if>"
			+ "</script>")
	List<Map<String, Object>> querySubject3(String teachername, String teacherid, String subname);
}
